package com.server.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Vector;

import com.entity.Announcement;
import com.entity.Campus;
import com.entity.Class;
import com.entity.Collage;
import com.entity.Department;
import com.entity.Grade;
import com.entity.Major;
import com.entity.Student;
import com.entity.Teacher;
import com.util.CommonUtil;

public class RoatDao extends BasicDao {

	public Vector<Vector<Object>> campusesQuerry(){
		String sql="select * from campus";
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> campuses=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
				//Campus campus=new Campus(rs.getString("campus_no"),rs.getString("name"), rs.getString("loc"));
			 Vector<Object> campus=new Vector<Object>();
			   campus.add(rs.getString("campus_no"));
			   campus.add(rs.getString("name"));
			   campus.add(rs.getString("loc"));
			   campuses.add(campus);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return campuses;
	}
	public Campus campusQuerry(String name){
		String sql="select * from campus where name='"+name+"'";
		
		ResultSet rs=this.query(sql);
		Campus campus=null;
		try {
			if(rs!=null){
				rs.next();
				campus=new Campus(rs.getString("campus_no"),rs.getString("name"), rs.getString("loc"));
			}
			//this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return campus;
	}
	public int updateCampus(Campus campus) {
		String sql="update campus set name='"+campus.getCampusName()+"',loc='"+campus.getLoc()+"' where campus_no='"+campus.getCampusno()+"'";
		int row=this.amdOpr(sql);
		return row;
	}
	public int addCampus(Campus campus) {
		String sql="insert into campus values('"+campus.getCampusno()+"','"+campus.getCampusName()+"','"+campus.getLoc()+"')";
		int row=this.amdOpr(sql);
		return row;
	}
	public int delCampus(ArrayList<String> campusNoList) {
		StringBuffer sb=new StringBuffer("delete from campus where name in(''");
		for(String campusno:campusNoList){
			sb.append(",'"+campusno+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryCollage(String campusNo) {
		String sql="select * from Collage where campus_no='"+campusNo+"'";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> collages=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> collage=new Vector<Object>();
			 collage.add(rs.getString("collage_no"));
			 collage.add(rs.getString("name"));
			 collage.add(rs.getString("campus_no"));
			 collages.add(collage);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return collages;
	}
	public Collage collageQuerry(String name) {
        String sql="select * from collage where name='"+name+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Collage collage=null;
		try {
			if(rs!=null){
				rs.next();
				collage=new Collage(rs.getString("collage_no"),rs.getString("name") ,rs.getString("campus_no"));
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return collage;
	}
	public int updateCollage(Collage collage) {
		String sql="update collage set name='"+collage.getName()+"',campus_no='"+collage.getCampusNo()+"' where collage_no='"+collage.getCollageNo()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int addCollage(Collage collage) {
		String sql="insert into collage values('"+collage.getCollageNo()+"','"+collage.getCampusNo()+"','"+collage.getName()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delCollage(ArrayList<String> nameList) {
		StringBuffer sb=new StringBuffer("delete from collage where name in(''");
		for(String name:nameList){
			sb.append(",'"+name+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryDepartments(String collageNo) {
		String sql="select * from department where collage_no='"+collageNo+"'";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> departments=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> department=new Vector<Object>();
			 department.add(rs.getString("dept_no"));
			 department.add(rs.getString("name"));
			 department.add(rs.getString("collage_no"));
			 departments.add(department);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return departments;
	}
	public Department departmentQuerry(String name) {
		String sql="select * from department where name='"+name+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Department department=null;
		try {
			if(rs!=null){
				rs.next();
				department=new Department(rs.getString("dept_no"),rs.getString("collage_no"),rs.getString("name") );
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return department;
	}
	public int updateDepartment(Department department) {
		String sql="update department set name='"+department.getName()+"',collage_no='"+department.getCollageNo()+"' where dept_no='"+department.getDeptNo()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int addDepartment(Department department) {
		String sql="insert into department values('"+department.getDeptNo()+"','"+department.getCollageNo()+"','"+department.getName()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delDepartment(ArrayList<String> deptNameList) {
		StringBuffer sb=new StringBuffer("delete from department where name in(''");
		for(String name:deptNameList){
			sb.append(",'"+name+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryMajors(String deptNo) {
		String sql="select * from major where dept_no='"+deptNo+"'";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> majors=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> major=new Vector<Object>();
			 major.add(rs.getString("major_no"));
			 major.add(rs.getString("name"));
			 major.add(rs.getString("dept_no"));
			 majors.add(major);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return majors;
	}
	public Major majorQuery(String name) {
		String sql="select * from major where name='"+name+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Major major=null;
		try {
			if(rs!=null){
				rs.next();
				major=new Major(rs.getString("major_no"),rs.getString("name"),rs.getString("dept_no") );
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return major;
	}
	public int updateMajor(Major major) {
		String sql="update major set name='"+major.getName()+"',dept_no='"+major.getDeptNo()+"' where major_no='"+major.getMajorNo()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int addMajor(Major major) {
		String sql="insert into major values('"+major.getMajorNo()+"','"+major.getDeptNo()+"','"+major.getName()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delMajor(ArrayList<String> nameList) {
		StringBuffer sb=new StringBuffer("delete from major where name in(''");
		for(String name:nameList){
			sb.append(",'"+name+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
}
	public Grade GradeQuery(String name) {
		String sql="select * from grade where name='"+name+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Grade grade=null;
		try {
			if(rs!=null){
				rs.next();
				grade=new Grade(rs.getString("grade_no"),rs.getString("name"));
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return grade;
	}
	public int addGrade(Grade grade) {
		String sql="insert into grade values('"+grade.getGradeNo()+"','"+grade.getName()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delGrade(ArrayList<String> nameList) {
		StringBuffer sb=new StringBuffer("delete from grade where name in(''");
		for(String name:nameList){
			sb.append(",'"+name+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int updateGrade(Grade grade) {
		String sql="update grade set name='"+grade.getName()+"' where grade_no='"+grade.getGradeNo()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryGrades() {
		String sql="select * from grade";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> grades=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> grade=new Vector<Object>();
			 grade.add(rs.getString("grade_no"));
			 grade.add(rs.getString("name"));
			 grades.add(grade);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return grades;
	}
	
	public Vector<Vector<Object>> queryClasses(String mgNo) {
		String majorNo=mgNo.split(",")[0];
		String gradeNo=mgNo.split(",")[1];
		String sql="select * from class where major_no='"+majorNo+"' and grade_no='"+gradeNo+"'";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> classes=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> cla=new Vector<Object>();
			 cla.add(rs.getString("class_no"));
			 cla.add(rs.getString("name"));
			 cla.add(rs.getString("major_no"));
			 cla.add(rs.getString("grade_no"));
			 classes.add(cla);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return classes;
	}
	public Class claQuery(String name) {
		String sql="select * from class where name='"+name+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Class cla=null;
		try {
			if(rs!=null){
				rs.next();
				cla=new Class(rs.getString("class_no"),rs.getString("name"),rs.getString("major_no"),rs.getString("grade_no") );
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return cla;
	}
	public int updateClass(Class cla) {
		String sql="update class set name='"+cla.getName()+"' where class_no='"+cla.getClassNo()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int addClass(Class cla) {
		String sql="insert into class values('"+cla.getClassNo()+"','"+cla.getGradeNo()+"','"+cla.getMajorNo()+"','"+cla.getName()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delClass(ArrayList<String> nameList) {
		StringBuffer sb=new StringBuffer("delete from class where name in(''");
		for(String name:nameList){
			sb.append(",'"+name+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryAnnouncements() {
		String sql="select * from announcement";
		System.out.println(sql);
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> announcements=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> announcement=new Vector<Object>();
			 announcement.add(rs.getString("title"));
			 announcement.add(rs.getString("create_time"));
			 switch (rs.getInt("view")) {
			case 1:announcement.add("学生");
				break;
			case 2:announcement.add("教师");
			break;
			case 3:announcement.add("管理员");
			break;
			case 4:announcement.add("师生");
			break;
			default:announcement.add("全体");
				break;
			}
			 switch (rs.getInt("releas_state")) {
				case 0:announcement.add("未发布");
					break;
				case 1:announcement.add("已发布");
				break;
				case 2:announcement.add("已撤销");
				break;
				}
			 announcements.add(announcement);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return announcements;
	}
	public Announcement announcementQuery(String title) {
		String sql="select * from announcement where title='"+title+"'";
        System.out.println(sql);
		ResultSet rs=this.query(sql);
		Announcement announcement=null;
		try {
			if(rs!=null){
				rs.next();
				announcement=new Announcement(rs.getString("announce_no"),rs.getString("title"),
						rs.getString("content"),rs.getString("create_time"),rs.getInt("releas_state"), rs.getInt("view"),
						rs.getString("remark"),rs.getString("user_id"));
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return announcement;
	}
	public int updateAnnouncement(Announcement announcement) {
		String sql="update announcement set title='"+announcement.getTitle()+"',content='"+announcement.getContent()+
				"',create_time='"+announcement.getCreateTime()+"',user_id='"+announcement.getUser_id()+
				"',releas_state="+announcement.getReleas_state()+",remark='"+announcement.getRemark()+
				"',view="+announcement.getView()+" where announce_no='"+announcement.getAnnounce_no()+"'";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int addAnnouncement(Announcement announcement) {
		String sql="insert into announcement values('"+announcement.getAnnounce_no()+"','"+announcement.getUser_id()+"','"+announcement.getTitle()+
				"','"+announcement.getContent()+"','"+announcement.getCreateTime()+"',"+announcement.getReleas_state()+
				","+announcement.getView()+",'"+announcement.getRemark()+"')";
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public int delAnnouncements(ArrayList<String> titleList) {
		StringBuffer sb=new StringBuffer("delete from announcement where title in(''");
		for(String title:titleList){
			sb.append(",'"+title+"'");
		}
		sb.append(")");
		String sql=sb.toString();
		System.out.println(sql);
		int row=this.amdOpr(sql);
		return row;
	}
	public Vector<Vector<Object>> queryTeachers() {
		String sql="select * from teacher";
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> teachers=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> teacher=new Vector<Object>();
			 teacher.add(rs.getString("tea_no"));
			 teacher.add(rs.getString("name"));
			 teacher.add(rs.getString("sex"));
			 String birth=rs.getString("birth");
			 teacher.add(CommonUtil.getAge(birth));
			 teacher.add(rs.getString("educate"));
			 teacher.add(rs.getString("job_grade"));
			 teachers.add(teacher);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return teachers;
	}
	public Teacher teacherQuery(String object) {
		// TODO Auto-generated method stub
		return null;
	}
	public int updateTeacher(Teacher object) {
		// TODO Auto-generated method stub
		return 0;
	}
	public int delTeachers(ArrayList<String> object) {
		// TODO Auto-generated method stub
		return 0;
	}
	public Vector<Vector<Object>> queryStudents() {
		String sql="select stu_no,s.name,sex,birth,c.name,political_status from student s,class c where s.class_no=c.class_no";
		ResultSet rs=this.query(sql);
		Vector<Vector<Object>> students=new Vector<Vector<Object>>();
		try {
			while(rs.next()){
			 Vector<Object> student=new Vector<Object>();
			 student.add(rs.getString("stu_no"));
			 student.add(rs.getString("name"));
			 student.add(rs.getString("sex"));
			 String birth=rs.getString("birth");
			 student.add(CommonUtil.getAge(birth));
			 student.add(rs.getString("c.name"));
			 student.add(rs.getString("political_status"));
			 students.add(student);
			}
			this.closeAll(rs, (PreparedStatement)rs.getStatement(), rs.getStatement().getConnection());
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return students;
	}
	
	
	public int updateStudent(com.entity.Student object) {
		// TODO Auto-generated method stub
		return 0;
	}
	public int delStudents(ArrayList<String> object) {
		// TODO Auto-generated method stub
		return 0;
	}
	public Student studentQuery(String object) { 
		// TODO Auto-generated method stub
		return null;
	}
}